﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;

namespace Wild2.Models.Topic
{
    public class Topic
    {

        public static bool VotePlus(int userID, int topicID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [TopicVotes] WHERE ([TopicID] = @TID) AND ([Kto] = @UID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [TopicVotes]([Kto], [TopicID], [Val]) VALUES(@UID, @TID, 1)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE BlogTopics SET Rating = (SELECT SUM(Val) as S FROM TopicVotes WHERE ([TopicID] = @TID)) WHERE (ID = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }

            return true;
        }

        public static bool VoteMinus(int userID, int topicID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [TopicVotes] WHERE ([TopicID] = @TID) AND ([Kto] = @UID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [TopicVotes]([Kto], [TopicID], [Val]) VALUES(@UID, @TID, -1)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE BlogTopics SET Rating = (SELECT SUM(Val) as S FROM TopicVotes WHERE ([TopicID] = @TID)) WHERE (ID = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            return true;
        }

        private static bool VoteCheckStar(int topicID)
        {
            int countVotes = 0;
            int minusVotes = 0;
            bool star = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Star] FROM [BlogTopics] WHERE ([ID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                star = Convert.ToBoolean(cmd.ExecuteScalar());
                cmd.Connection.Close();
            }
            if (!star)
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [TopicVotes] WHERE ([TopicID] = @TID)", conn);
                    cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                    cmd.Connection.Open();
                    countVotes = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Connection.Close();
                }
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [TopicVotes] WHERE ([TopicID] = @TID) AND (Val < 0)", conn);
                    cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                    cmd.Connection.Open();
                    minusVotes = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Connection.Close();
                }
                if (countVotes > 10)
                {
                    if (100 - (((double)minusVotes / (double)countVotes) * 100) > 80) //80% голосов +
                    {
                        int uid = -1;
                        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand(@"SELECT [User_ID] FROM [BlogTopics] WHERE ([ID] = @TID)", conn);
                            cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                            cmd.Connection.Open();
                            uid = Convert.ToInt32(cmd.ExecuteScalar());
                            cmd.Connection.Close();
                        }
                        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand(@"INSERT INTO [Invites]([Kto], [Code]) VALLUES(@Kto, @Code)", conn);
                            cmd.Parameters.Add("@Kto", SqlDbType.Int).Value = uid;
                            cmd.Parameters.Add("@Code", SqlDbType.NVarChar, 50).Value = Guid.NewGuid().ToString().Split(new char[] { '-' })[0];
                            cmd.Connection.Open();
                            cmd.ExecuteNonQuery();
                            cmd.Connection.Close();
                        }
                        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand(@"UPDATE [BlogTopics] SET [Star] = 'TRUE' FROM  WHERE ([ID] = @TID)", conn);
                            cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                            cmd.Connection.Open();
                            star = Convert.ToBoolean(cmd.ExecuteScalar());
                            cmd.Connection.Close();
                        }
                    }
                }
            }
            return false;
        }



        public static bool VoteSandboxPlus(int userID, int topicID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [SandboxTopicVotes] WHERE ([TopicID] = @TID) AND ([Kto] = @UID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [SandboxTopicVotes]([Kto], [TopicID], [Val]) VALUES(@UID, @TID, 1)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE SandboxTopics SET Rating = (SELECT SUM(Val) as S FROM SandboxTopicVotes WHERE ([TopicID] = @TID)) WHERE (ID = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }

            return true;
        }

        public static bool VoteSandboxMinus(int userID, int topicID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"DELETE FROM [SandboxTopicVotes] WHERE ([TopicID] = @TID) AND ([Kto] = @UID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO [SandboxTopicVotes]([Kto], [TopicID], [Val]) VALUES(@UID, @TID, -1)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE SandboxTopics SET Rating = (SELECT SUM(Val) as S FROM SandboxTopicVotes WHERE ([TopicID] = @TID)) WHERE (ID = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            return true;
        }

        private static bool VoteSandboxCheckStar(int topicID)
        {
            int countVotes = 0;
            int minusVotes = 0;
            bool star = false;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Star] FROM [SandboxTopics] WHERE ([ID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                star = Convert.ToBoolean(cmd.ExecuteScalar());
                cmd.Connection.Close();
            }
            if (!star)
            {
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [SandboxTopicVotes] WHERE ([TopicID] = @TID)", conn);
                    cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                    cmd.Connection.Open();
                    countVotes = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Connection.Close();
                }
                using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                {
                    SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [SandboxTopicVotes] WHERE ([TopicID] = @TID) AND (Val < 0)", conn);
                    cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                    cmd.Connection.Open();
                    minusVotes = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Connection.Close();
                }
                if (countVotes > 10)
                {
                    if (100 - (((double)minusVotes / (double)countVotes) * 100) > 80) //80% голосов +
                    {
                        //Send email with invite
                        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
                        {
                            SqlCommand cmd = new SqlCommand(@"UPDATE [SandboxTopics] SET [Star] = 'TRUE' FROM  WHERE ([ID] = @TID)", conn);
                            cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                            cmd.Connection.Open();
                            star = Convert.ToBoolean(cmd.ExecuteScalar());
                            cmd.Connection.Close();
                        }
                    }
                }
            }
            return false;
        }



        public static int GetTopicIDFromMID(int MessageID)
        {
            int tid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [ID_Topic] FROM [BlogComments] WHERE ([ID] = @MID)", conn);
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = MessageID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tid = Convert.ToInt32(reader["ID_Topic"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return tid;
        }

        public static int GetSandboxTopicIDFromMID(int MessageID)
        {
            int tid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [ID_Topic] FROM [SandboxComments] WHERE ([ID] = @MID)", conn);
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = MessageID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tid = Convert.ToInt32(reader["ID_Topic"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return tid;
        }

        public static TopicDataFull GetTopicInfo(int topicID)
        {
            TopicDataFull tf = new TopicDataFull();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT	[BlogTopics].[Caption], 
		                                                    [BlogTopics].[Comments_Count], 
		                                                    [BlogTopics].[Date], 
		                                                    [BlogTopics].[ID], 
		                                                    [BlogTopics].[ID_Blog], 
		                                                    [BlogTopics].[isClosed], 
		                                                    [BlogTopics].[isPublic], 
		                                                    [BlogTopics].[isCommentsHidden], 
		                                                    [BlogTopics].[isTop], 
		                                                    [BlogTopics].[Rating], 
		                                                    [BlogTopics].[Shows_Count], 
		                                                    [BlogTopics].[Text], 
		                                                    [BlogTopics].[Text_Large], 
                                                            [BlogTopics].[Star], 
		                                                    [Users].[ID] as UID, 
		                                                    [Users].[Nick]
                                                    FROM [BlogTopics] 
                                                    INNER JOIN [Users]
                                                    ON [BlogTopics].[User_ID] = [Users].[ID] 
                                                    WHERE ([BlogTopics].[ID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tf.Caption = reader["Caption"].ToString();
                    tf.CommentsCount = Convert.ToInt32(reader["Comments_Count"]);
                    tf.Date = Convert.ToDateTime(reader["Date"]);
                    tf.ID = Convert.ToInt32(reader["ID"]);
                    tf.ID_Blog = Convert.ToInt32(reader["ID_Blog"]);
                    tf.isClosed = Convert.ToBoolean(reader["isClosed"]);
                    tf.isPublic = Convert.ToBoolean(reader["isPublic"]);
                    tf.isCommentsHidden = Convert.ToBoolean(reader["isCommentsHidden"]);
                    tf.isTop = Convert.ToBoolean(reader["isTop"]);
                    tf.Rating = Convert.ToInt32(reader["Rating"]);
                    tf.ShowsCount = Convert.ToInt32(reader["Shows_Count"]);
                    tf.Text = reader["Text"].ToString();
                    tf.TextLarge = reader["Text_Large"].ToString();
                    tf.UserID = Convert.ToInt32(reader["UID"]);
                    tf.UserNick = reader["Nick"].ToString();
                    tf.Star = Convert.ToBoolean(reader["Star"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            tf.Tags = GetTopicTags(topicID);
            return tf;
        }

        public static SandboxTopicDataFull GetSandboxTopicInfo(int topicID)
        {
            SandboxTopicDataFull tf = new SandboxTopicDataFull();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT	[SandBoxTopics].[ID], 
		                                                    [SandBoxTopics].[Rating], 
		                                                    [SandBoxTopics].[EMail], 
		                                                    [SandBoxTopics].[Shows_Count], 
		                                                    [SandBoxTopics].[Comments_Count], 
		                                                    [SandBoxTopics].[Date], 
		                                                    [SandBoxTopics].[Text], 
		                                                    [SandBoxTopics].[Nick], 
		                                                    [SandBoxTopics].[Star], 
		                                                    [SandBoxTopics].[Caption],
                                                            [SandBoxTopics].[UserID]
                                                    FROM [SandBoxTopics] 
                                                    WHERE ([SandBoxTopics].[ID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tf.ID = Convert.ToInt32(reader["ID"]);
                    tf.Rating = Convert.ToInt32(reader["Rating"]);
                    tf.EMail = reader["EMail"].ToString();
                    tf.ShowsCount = Convert.ToInt32(reader["Shows_Count"]);
                    tf.CommentsCount = Convert.ToInt32(reader["Comments_Count"]);
                    tf.Date = Convert.ToDateTime(reader["Date"]);
                    tf.Text = reader["Text"].ToString();
                    tf.UserNick = reader["Nick"].ToString();
                    tf.Star = Convert.ToBoolean(reader["Star"]);
                    tf.Caption = reader["Caption"].ToString();
                    if (reader["UserID"] != DBNull.Value) tf.UserID = Convert.ToInt32(reader["UserID"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return tf;
        }

        public static int GetSandboxTopicCountComments(int topicID)
        {
            int count = 0;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT
                                                    FROM [SandBoxComments] 
                                                    WHERE ([SandBoxComments].[ID_Topic] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    count = Convert.ToInt32(reader["CNT"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return count;
        }

        public static List<String> GetTopicTags(int topicID)
        {
            List<String> list = new List<String>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [ID], [TagText] FROM [BlogTags] WHERE ([ID_Topic] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["TagText"].ToString());
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return list;
        }

        public static int AddTopicComment(int kto, int topicID, String Text)
        {
            if (Text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Комментарий", 500);
            if (Text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Комментарий", 3);
            int cid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"AddBlogComment", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = kto;
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000).Value = Text;
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Connection.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["id"] != DBNull.Value)
                    {
                        cid = Convert.ToInt32(reader["id"]);
                    }
                    else
                    {
                        int err = Convert.ToInt32(reader["mess"]);
                        if (err == -4) throw new Wild2.Models.Exceptions.TextException("Тема закрыта");
                        if (err == -6) throw new Wild2.Models.Exceptions.TextException("Слишком большой уровень вложенности комментариев");
                    }
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return cid;
        }

        public static int AddSandboxTopicComment(Int32? userID, String kto, int topicID, String Text)
        {
            if (Text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Комментарий", 500);
            if (Text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Комментарий", 3);
            if (kto.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Ник", 4000);
            if (kto.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Ник", 3);
            int cid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"AddSandboxComment", conn);
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 50).Value = kto;
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000).Value = Text;
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = DBNull.Value;
                if (userID.HasValue) cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID.Value;
                else cmd.Parameters.Add("@UID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Connection.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["id"] != DBNull.Value)
                    {
                        cid = Convert.ToInt32(reader["id"]);
                    }
                    else
                    {
                        int err = Convert.ToInt32(reader["mess"]);
                        if (err == -6) throw new Wild2.Models.Exceptions.TextException("Слишком большой уровень вложенности комментариев");
                    }
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return cid;
        }

        public static int AddTopicCommentAnswer(int kto, int messageID, String Text)
        {
            if (Text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Комментарий", 500);
            if (Text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Комментарий", 3);
            int cid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"AddBlogComment", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = kto;
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000).Value = Text;
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = messageID;
                cmd.Connection.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["id"] != DBNull.Value)
                    {
                        cid = Convert.ToInt32(reader["id"]);
                    }
                    else
                    {
                        int err = Convert.ToInt32(reader["mess"]);
                        if (err == -4) throw new Wild2.Models.Exceptions.TextException("Тема закрыта");
                        if (err == -6) throw new Wild2.Models.Exceptions.TextException("Слишком большой уровень вложенности комментариев");
                    }
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return cid;
        }

        public static int AddSandboxTopicCommentAnswer(Int32? userID, string kto, int messageID, String Text)
        {
            if (Text.Length > 500) throw new Wild2.Models.Exceptions.TextLongException("Комментарий", 500);
            if (Text.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Комментарий", 3);
            if (kto.Length > 50) throw new Wild2.Models.Exceptions.TextLongException("Ник", 4000);
            if (kto.Length < 3) throw new Wild2.Models.Exceptions.TextShortException("Ник", 3);
            int cid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"AddSandboxComment", conn);
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 50).Value = kto;
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000).Value = Text;
                cmd.Parameters.Add("@MID", SqlDbType.Int).Value = messageID;
                if (userID.HasValue) cmd.Parameters.Add("@UID", SqlDbType.Int).Value = userID.Value;
                else cmd.Parameters.Add("@UID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Connection.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader["id"] != DBNull.Value)
                    {
                        cid = Convert.ToInt32(reader["id"]);
                    }
                    else
                    {
                        int err = Convert.ToInt32(reader["mess"]);
                        if (err == -4) throw new Wild2.Models.Exceptions.TextException("Тема закрыта");
                        if (err == -6) throw new Wild2.Models.Exceptions.TextException("Слишком большой уровень вложенности комментариев");
                    }
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return cid;
        }

        public static List<Comment.CommentData> GetTopicComments(int topicID)
        {
            List<Comment.CommentData> comments = new List<Comment.CommentData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT 
	                                                [BlogComments].[Level], 
	                                                [BlogComments].[isDeleted], 
	                                                [BlogComments].[ID] AS CID, 
	                                                [Users].[ID], 
	                                                [Users].[Nick], 
	                                                [Users].[LastOnlineDate], 
	                                                [BlogComments].[Date], 
	                                                [BlogComments].[Text], 
	                                                [BlogComments].[KomyMessageID]
                                                FROM 
	                                                [BlogComments] 
                                                INNER JOIN [Users] ON [BlogComments].[ID_User] = [Users].[ID]
                                                WHERE ([BlogComments].[ID_Topic] = @TID)
                                                ORDER BY [BlogComments].[Level]", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Comment.CommentData cd = new Comment.CommentData();
                    cd.Level = reader["Level"].ToString();
                    cd.isDeleted = Convert.ToBoolean(reader["isDeleted"]);
                    cd.ID = Convert.ToInt32(reader["CID"]);
                    cd.UserID = Convert.ToInt32(reader["ID"]);
                    cd.Nick = reader["Nick"].ToString();
                    cd.LastOnlineDate = Convert.ToDateTime(reader["LastOnlineDate"]);
                    cd.Date = Convert.ToDateTime(reader["Date"]);
                    cd.Text = reader["Text"].ToString();
                    if (reader["KomyMessageID"] != DBNull.Value)
                    {
                        cd.Komy = Convert.ToInt32(reader["KomyMessageID"]);
                    }
                    cd.TopicID = topicID;
                    comments.Add(cd);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return comments;
        }

        public static List<Comment.CommentData> GetSandboxTopicComments(int topicID, int page)
        {
            List<Comment.CommentData> comments = new List<Comment.CommentData>();
            int elOnPage = 10;
            if (ConfigurationManager.AppSettings["elonpage"] != null) { elOnPage = Int32.Parse(ConfigurationManager.AppSettings["elonpage"]) * 2; }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT       [Level], [isDeleted], [ID], [Nick], [Date], [Nick], [Text], [KomyMessageID], [UserID]
                                                FROM (
                                                      SELECT        TOP (100) PERCENT  ROW_NUMBER() OVER (ORDER BY [SandboxComments].[Level]) AS num, 
                                                      [SandboxComments].[Level], 
                                                      [SandboxComments].[isDeleted], 
                                                      [SandboxComments].[ID], 
                                                      [SandboxComments].[Nick], 
                                                      [SandboxComments].[Date], 
                                                      [SandboxComments].[Text], 
                                                      [SandboxComments].[KomyMessageID],
                                                      [SandboxComments].[UserID]
                                                    FROM 
                                                      [SandboxComments] 
                                                    WHERE ([SandboxComments].[ID_Topic] = @TID)) T
                                                WHERE        (num > @KolZap1) AND (num <= @KolZap2)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Parameters.Add("@KolZap1", SqlDbType.Int);
                cmd.Parameters.Add("@KolZap2", SqlDbType.Int);
                cmd.Parameters["@KolZap1"].Value = ((page - 1) * elOnPage);
                cmd.Parameters["@KolZap2"].Value = (((page - 1) * elOnPage) + elOnPage);
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Comment.CommentData cd = new Comment.CommentData();
                    cd.Level = reader["Level"].ToString();
                    cd.isDeleted = Convert.ToBoolean(reader["isDeleted"]);
                    cd.ID = Convert.ToInt32(reader["ID"]);
                    cd.Nick = reader["Nick"].ToString();
                    cd.Date = Convert.ToDateTime(reader["Date"]);
                    cd.Text = reader["Text"].ToString();
                    if (reader["UserID"] != DBNull.Value) cd.UserID = Convert.ToInt32(reader["UserID"]);
                    if (reader["KomyMessageID"] != DBNull.Value) cd.Komy = Convert.ToInt32(reader["KomyMessageID"]);
                    cd.TopicID = topicID;
                    comments.Add(cd);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return comments;
        }

        public static List<Wild2.Models.Votes.VoteData> GetVotesList(int topicID)
        {
            List<Wild2.Models.Votes.VoteData> votes = new List<Votes.VoteData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Kto], [Nick], [Val] FROM [TopicVotes] INNER JOIN [Users] ON [TopicVotes].[Kto] = [Users].[ID] WHERE ([TopicID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    votes.Add(new Votes.VoteData() { TopicID = topicID, UserID = Convert.ToInt32(reader["kto"]), UserNick = reader["Nick"].ToString(), Vote = Convert.ToInt32(reader["val"]) });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return votes;
        }

        public static List<Wild2.Models.Votes.VoteData> GetSandboxVotesList(int topicID)
        {
            List<Wild2.Models.Votes.VoteData> votes = new List<Votes.VoteData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT [Kto], [Nick], [Val] FROM [SandboxTopicVotes] INNER JOIN [Users] ON [SandboxTopicVotes].[Kto] = [Users].[ID] WHERE ([TopicID] = @TID)", conn);
                cmd.Parameters.Add("@TID", SqlDbType.Int).Value = topicID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    votes.Add(new Votes.VoteData() { TopicID = topicID, UserID = Convert.ToInt32(reader["kto"]), UserNick = reader["Nick"].ToString(), Vote = Convert.ToInt32(reader["val"]) });
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return votes;
        }
    }
}